<?php

include 'DBconn.php';

class PurchaseOrder {

    public function getItemsForGr($supplierFk) 
	{
        $ret = array();
        $query = "Select ph.po_serialnumber,ph.po_id,pd.po_detserialnumber,pd.sc_detserialnumber_fk,sh.sales_con_code,pd.pro_serialnumber_fk,pd.pro_qty,pd.warehouse_serialnumber_fk, 
		pd.pro_qty - IFNULL((Select Sum(received_qty) from grn_detail where activestatus=1 and po_detserialnumber_fk=pd.po_detserialnumber GROUP BY po_detserialnumber_fk),0) as balance_qty,
		pro.pro_name,pro.part_number,pro.pro_description,round((pd.unit_price * ph.current_rate),4) as unit_price from po_header as ph LEFT JOIN po_details as pd ON pd.activestatus=1 
		and pd.pro_status!='FULLY SATISFIED' and pd.po_serialnumber_fk=ph.po_serialnumber LEFT JOIN product_header as pro ON pro.activestatus=1 and pro.serialnumber=pd.pro_serialnumber_fk 
		LEFT JOIN sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.activestatus=1 and sh.serialnumber=sd.sc_serialnumber_fk 
		where ph.activestatus=1 and ph.po_status!='DRAFT' and ph.po_status!='FULLY SATISFIED' and ph.po_status!='VOID' and 
		ph.supplier_serialnumber_fk='$supplierFk' HAVING balance_qty>0 ORDER BY ph.po_serialnumber";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoStatePurchaseOrder();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
			$temp->po_detserialnumber = (int) $row->po_detserialnumber;
			$temp->sc_detserialnumber_fk = (int) $row->sc_detserialnumber_fk;
			$temp->sales_con_code = $row->sales_con_code;
			$temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
			$temp->pro_name =  $row->pro_name;
			$temp->part_number =  $row->part_number;
			$temp->pro_description =  $row->pro_description;	
			$temp->po_qty = (int) $row->pro_qty;
			$temp->balance_qty = (int) $row->balance_qty;
			$temp->unit_price = (double) $row->unit_price;
			$temp->warehouse_serialnumber_fk = (int) $row->warehouse_serialnumber_fk;
			$temp->isSelected = false;
			$temp->sampe_po = true;
			
            $ret[] = $temp;
        }
		 $query2 = "Select ph.po_serialnumber,ph.po_id,pd.po_detserialnumber,pd.sc_detserialnumber_fk,sh.sales_con_code,pd.pro_serialnumber_fk,pd.pro_qty,pd.warehouse_serialnumber_fk, 
		pd.pro_qty - IFNULL((Select Sum(received_qty) from grn_detail where activestatus=1 and po_detserialnumber_fk=pd.po_detserialnumber GROUP BY po_detserialnumber_fk),0) as balance_qty,
		pro.pro_name,pro.part_number,pro.pro_description,round((pd.unit_price * ph.current_rate),4) as unit_price from po_header as ph LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.pro_serialnumber_fk IN(SELECT pd.pro_serialnumber_fk FROM po_details as pd LEFT JOIN po_header as ph ON pd.po_serialnumber_fk=ph.po_serialnumber and pd.pro_status!='FULLY SATISFIED' where ph.supplier_serialnumber_fk='$supplierFk') 
		and pd.pro_status!='FULLY SATISFIED' and pd.po_serialnumber_fk=ph.po_serialnumber LEFT JOIN product_header as pro ON pro.activestatus=1 and pro.serialnumber=pd.pro_serialnumber_fk 
		LEFT JOIN sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.activestatus=1 and sh.serialnumber=sd.sc_serialnumber_fk 
		where ph.activestatus=1 and ph.po_status!='DRAFT' and ph.po_status!='FULLY SATISFIED' and ph.po_status!='VOID' and 
		ph.supplier_serialnumber_fk!='$supplierFk' HAVING balance_qty>0  ORDER BY ph.po_serialnumber";
		$s2 = mysql_query($query2) or die(mysql_error());
        while ($row = mysql_fetch_object($s2)) {
		  $temp2 = new VoStatePurchaseOrder();
            $temp2->po_serialnumber = (int) $row->po_serialnumber;
            $temp2->po_id = $row->po_id;
			$temp2->po_detserialnumber = (int) $row->po_detserialnumber;
			$temp2->sc_detserialnumber_fk = (int) $row->sc_detserialnumber_fk;
			$temp2->sales_con_code = $row->sales_con_code;
			$temp2->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
			$temp2->pro_name =  $row->pro_name;
			$temp2->part_number =  $row->part_number;
			$temp2->pro_description =  $row->pro_description;	
			$temp2->po_qty = (int) $row->pro_qty;
			$temp2->balance_qty = (int) $row->balance_qty;
			$temp2->unit_price = (double) $row->unit_price;
			$temp2->warehouse_serialnumber_fk = (int) $row->warehouse_serialnumber_fk;
			$temp2->isSelected = false;
			$temp2->sampe_po = false;
			$ret[] = $temp2;
		}
        return $ret;
    }
	
	public function addNewScFromPO($VObject) 
	{
		$tempSc = new VoInsertedSc();
		$sales_con_code = "";
		
		if($VObject->customer_serialnumber_fk==0)
		{
			$sales_con_code = $this->generateScSelfCode();
			mysql_query("insert into sc_self_code(self_code) values ('$sales_con_code')") or die(mysql_error());
		}
		
        $query = "insert into sales_conformation_header(sales_con_code,customer_serialnumber_fk,total_amount,total_qty,sc_term,sc_gst,currency_serialnumber_fk,current_rate,sales_con_status,sales_con_date,no_of_days,expected_datetime,sc_remarks,cre_userid_fk,cre_datetime,activestatus) VALUES ('$sales_con_code','$VObject->customer_serialnumber_fk','$VObject->total_amount','$VObject->total_qty','$VObject->sc_term','$VObject->sc_gst','$VObject->currency_serialnumber_fk','$VObject->current_rate','$VObject->sales_con_status','$VObject->sales_con_date','$VObject->no_of_days','$VObject->expected_datetime','$VObject->sc_remarks','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) {
		
            $last_id = mysql_insert_id();		
			$tempSc->sales_con_code=$sales_con_code;
			
            foreach ($VObject->pro_serialnumber_fk as $r => $value) {
				$pro_detserialnumber = mysql_real_escape_string($VObject->pro_detserialnumber[$r]);
				$base_currency_detserialnumber_fk = mysql_real_escape_string($VObject->base_currency_detserialnumber_fk[$r]);
                $price_per_unit = mysql_real_escape_string($VObject->price_per_unit[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
                $delivery_loc = mysql_real_escape_string($VObject->delivery_loc[$r]);
                $sales_type = mysql_real_escape_string($VObject->sales_type[$r]);
                $warehouse_serialnumber_fk = 0;//mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into sales_conformation_details(sc_serialnumber_fk,pro_serialnumber_fk,pro_detserialnumber,base_currency_detserialnumber_fk,pro_qty,price_per_unit,pro_total_price,pro_status,delivery_loc,warehouse_serialnumber_fk,sales_type,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$value','$pro_detserialnumber','$base_currency_detserialnumber_fk','$pro_qty','$price_per_unit','$pro_total_price','$pro_status','$delivery_loc','$warehouse_serialnumber_fk','$sales_type','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$tempSc->sc_detserialnumber_fk[] = (int)mysql_insert_id();
				$tempSc->pro_serialnumber_fk[] = $value;
            }
        }

       return $tempSc;
    }
	
	public function generateScSelfCode() {
        $res = "";
        $query = "select self_code from sc_self_code order by self_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("AESC", $row[0]);
            $num = intval($str[1]);
            $res = "AESC" . ++$num;
        } else {
            $res = 'AESC1001';
        }
        return $res;
    }
	
	public function getUnMappedIndentScList()
	{
	  $ret = array();
	  $result1 = mysql_query("Select sh.serialnumber,sh.sales_con_code,IF(sh.customer_serialnumber_fk=0,1,0) as self_sc,sd.detserialnumber,sd.pro_serialnumber_fk,sd.sales_type,pro.pro_name,pro.part_number,pro.stamp_number,pro.pro_description,pro.currency_serialnumber,pro.listprice,pro.discount,IF(sh.customer_serialnumber_fk!=0,IFNULL(sd.pro_qty - (IFNULL((Select Sum(pms.allocated_qty) from po_map_sc as pms where pms.activestatus=1 and pms.sc_detserialnumber=sd.detserialnumber),0) + IFNULL((Select Sum(pd.pro_qty) from po_details as pd where pd.activestatus=1 and pd.sc_detserialnumber_fk=sd.detserialnumber and pd.po_serialnumber_fk IN(Select po_serialnumber from po_header where activestatus=1 and po_status='DRAFT')),0)),0),IF(sd.detserialnumber Not IN(Select sc_detserialnumber from po_map_sc),sd.pro_qty,IFNULL((Select Sum(ssd.pro_qty) from sales_conformation_details as ssd where ssd.activestatus=1 and ssd.detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where (spms.self_sc=0 or spms.sc_detserialnumber=sd.detserialnumber) and spms.po_detserialnumber IN(Select po_detserialnumber from po_details where sc_detserialnumber_fk=sd.detserialnumber))),0) - ( IFNULL((Select Sum(spd.pro_qty) from po_details as spd where spd.activestatus=1 and spd.sc_detserialnumber_fk=sd.detserialnumber and spd.po_serialnumber_fk IN(Select po_serialnumber from po_header where activestatus=1 and po_status='DRAFT')),0) + IFNULL((Select Sum(psm.allocated_qty) 
	  from po_map_sc as psm where psm.activestatus=1 and psm.sc_detserialnumber IN(Select ssp.sc_detserialnumber from po_map_sc as ssp where ssp.activestatus=1 and ssp.po_detserialnumber IN(Select po_detserialnumber from po_details where activestatus=1 and sc_detserialnumber_fk=sd.detserialnumber)) ),0)) ) ) as balance_for_po, round((sd.price_per_unit * sh.current_rate),4) as price_per_unit from sales_conformation_details as sd LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk and sh.activestatus=1 LEFT JOIN product_header as pro ON pro.serialnumber=sd.pro_serialnumber_fk and pro.activestatus=1 where sd.activestatus=1 and sd.sales_type='Indent Sales' HAVING balance_for_po>0 ORDER BY sd.sc_serialnumber_fk") or die(mysql_error());
	  while ($row1 = mysql_fetch_object($result1))
	  {
	    $temp = new VoUnMappedIndentSc;
        $temp->sc_serialnumber = (int) $row1->serialnumber;
	    $temp->sales_con_code =  $row1->sales_con_code;
		$temp->self_sc = (int) $row1->self_sc;
	    $temp->sc_detserialnumber = (int) $row1->detserialnumber;
	    $temp->pro_qty = (int) $row1->balance_for_po;
	    $temp->pro_serialnumber_fk = (int) $row1->pro_serialnumber_fk;
	    $temp->pro_name =  $row1->pro_name;
	    $temp->part_number =  $row1->part_number;
		$temp->stamp_number =  $row1->stamp_number;
	    $temp->pro_description =  $row1->pro_description;	 
		
			$result = mysql_query("SELECT currency_value from currency_detail WHERE activestatus ='1'
			and to_currency_fk='$row1->currency_serialnumber' ORDER BY detserialnumber DESC LIMIT 1") or die(mysql_error());
			$row = mysql_fetch_row($result);
			$currency_value =(double)$row[0];
			
			if($row1->self_sc!=0)
			{
			    $temp->sgd_listprice = (double)$row1->price_per_unit;
			}else {
				$listPrice = (double)$row1->listprice;			
				$discount = (double)$row1->discount;	
				$temp->sgd_listprice = round((($listPrice - ($listPrice * $discount/100))* $currency_value),4);
			}
			
		$temp->isSelected = false;
		$ret[] = $temp;		
	  }
	  
	  return $ret;
	}
	
	public function addNewPOAndMapSc($VObject) 
	{
        $po_id = "";
        if ($VObject->po_status != "DRAFT") {
            $po_id = $this->generatePoCode();
            mysql_query("insert into po_code_table(po_code) values ('$po_id')") or die(mysql_error());
        } else {
            $po_id = "Draft";
        }
        $query = "insert into po_header(po_id,supplier_serialnumber_fk,total_amount,total_qty,po_term,po_gst,currency_serialnumber_fk,current_rate,po_issue_date,po_confirm_date,no_of_days,expected_datetime,po_status,po_remarks,cre_userid_fk,cre_datetime,activestatus) values('$po_id','$VObject->supplier_serialnumber_fk','$VObject->total_amount','$VObject->total_qty','$VObject->po_term','$VObject->po_gst','$VObject->currency_serialnumber_fk','$VObject->current_rate','$VObject->po_issue_date','$VObject->po_confirm_date','$VObject->no_of_days','$VObject->expected_datetime','$VObject->po_status','$VObject->po_remarks','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) 
		{
            $last_id = mysql_insert_id();
            foreach ($VObject->enq_detserialnumber_fk as $r => $value) {
                $query1 = "insert into enq_det_po(enq_detserialnumber_fk,po_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$value','$last_id','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query1) or die(mysql_error());
            }
           foreach ($VObject->pro_serialnumber_fk as $r => $value) 
		   {
				$sc_detserialnumber_fk = mysql_real_escape_string($VObject->sc_detserialnumber_fk[$r]);
				$self_sc = mysql_real_escape_string($VObject->self_sc[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $unit_price = mysql_real_escape_string($VObject->unit_price[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
                $warehouse_serialnumber_fk = mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into po_details(po_serialnumber_fk,sc_detserialnumber_fk,pro_serialnumber_fk,pro_qty,unit_price,pro_total_price,pro_status,warehouse_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$sc_detserialnumber_fk','$value','$pro_qty','$unit_price','$pro_total_price','$pro_status','$warehouse_serialnumber_fk','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$po_det_last_id = mysql_insert_id();
				
                if ($VObject->po_status != "DRAFT") 
				{
					if($self_sc==0)
					{
						$sc_type = 3;		
						
						$quantity = $pro_qty;
						$cancellation_number = array();
						$sc_detserialnumberArr = array();
						$cancelled_qtyArr = array();
						$sales_typeArr = array();
						
						$result = mysql_query("Select po_detserialnumber from po_details where sc_detserialnumber_fk='$sc_detserialnumber_fk' ORDER BY po_detserialnumber") or die(mysql_error());
						while($row = mysql_fetch_object($result))
						{
						   $result1 = mysql_query("Select cp.serialnumber,cp.sc_detserialnumber_fk,cp.cancelled_qty,if(sh.customer_serialnumber_fk!=0,if(sd.sales_type='Ex-Stock Sales',1,if(sd.sales_type='Advanced Sales',2,3)),0) as sc_type from cancelled_po as cp LEFT JOIN sales_conformation_details as sd ON sd.activestatus=1 and sd.detserialnumber=cp.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk 
						   where cp.activestatus=1 and cp.po_detserialnumber_fk='$row->po_detserialnumber' and cp.reactiveated=0") or die(mysql_error());
						   while($row1 = mysql_fetch_object($result1))
							{
								$cancellation_number[] = (int)$row1->serialnumber;
								$sc_detserialnumberArr[] = (int)$row1->sc_detserialnumber_fk;
								$cancelled_qtyArr[] = (int)$row1->cancelled_qty;
								$sales_typeArr[] = (int)$row1->sc_type;
							}
						}					
						
						foreach($sc_detserialnumberArr as $i => $sc_det_fk) 
						{
						    if($quantity>0)
							{
							  $query3 = "Insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
							  cre_datetime,activestatus) values('$po_det_last_id','$sc_det_fk','$cancelled_qtyArr[$i]','$sales_typeArr[$i]',0,'$VObject->cre_userid_fk',
							  '$VObject->cre_datetime',1)";
							  mysql_query($query3) or die(mysql_error());
							  $quantity = $quantity - $cancelled_qtyArr[$i];
							  mysql_query("Update cancelled_po set reactiveated='1' where serialnumber='$cancellation_number[$i]'") or die(mysql_error());
							}
						}
						
						if($quantity>0)
						{
							$query3 = "insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
							cre_datetime,activestatus) values('$po_det_last_id','$sc_detserialnumber_fk','$quantity','$sc_type',0,'$VObject->cre_userid_fk',
							'$VObject->cre_datetime',1)";
							mysql_query($query3) or die(mysql_error());
						}
						
						//Update Inorder Details For Customer
							$query4 = "select in_order from stock_header where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
							$result4 = mysql_query($query4) or die(mysql_error());
							while ($row4 = mysql_fetch_object($result4)) 
							{							
								$in_order = (int) $row4->in_order;
							
								$pre_inorder_qty = $in_order;
								
								$in_order = $in_order + $pro_qty;
								
								$post_inorder_qty = $in_order;
								
								$inorder_det_qty = $post_inorder_qty - $pre_inorder_qty;
								
								$query5 = "update stock_header set in_order='$in_order' where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
								$s5 = mysql_query($query5) or die(mysql_error());
								
								$query6 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$po_det_last_id',1,'$value',0,0,'$inorder_det_qty',0,0,1)";
								$s6 = mysql_query($query6) or die(mysql_error());
							}
					}else
						{		
							$quantity = $pro_qty;
							$cancellation_number = array();
							$sc_detserialnumberArr = array();
							$cancelled_qtyArr = array();
							$sales_typeArr = array();
							$po_detserialnumber = 0;
						
							$result = mysql_query("Select po_detserialnumber from po_details where sc_detserialnumber_fk='$sc_detserialnumber_fk' ORDER BY po_detserialnumber") or die(mysql_error());
							while($row = mysql_fetch_object($result))
							{
								$result1 = mysql_query("Select cp.serialnumber,cp.sc_detserialnumber_fk,cp.cancelled_qty,if(sh.customer_serialnumber_fk!=0,if(sd.sales_type='Ex-Stock Sales',1,if(sd.sales_type='Advanced Sales',2,3)),0) as sc_type from cancelled_po as cp LEFT JOIN sales_conformation_details as sd ON sd.activestatus=1 and sd.detserialnumber=cp.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk 
								where cp.activestatus=1 and cp.po_detserialnumber_fk='$row->po_detserialnumber' and cp.reactiveated=0") or die(mysql_error());
								while($row1 = mysql_fetch_object($result1))
								{
									$cancellation_number[] = (int)$row1->serialnumber;
									$sc_detserialnumberArr[] = (int)$row1->sc_detserialnumber_fk;
									$cancelled_qtyArr[] = (int)$row1->cancelled_qty;
									$sales_typeArr[] = (int)$row1->sc_type;
								}
							}	
							
							$tot_inorder = 0;
							$tot_free_allocation = 0;
							
							foreach($sc_detserialnumberArr as $i => $sc_det_fk) 
							{
								if($quantity>0)
								{
									$query3 = "Insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) 
									values('$po_det_last_id','$sc_det_fk','$cancelled_qtyArr[$i]','$sales_typeArr[$i]',IF($sales_typeArr[$i]!=0,0,1),'$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
									mysql_query($query3) or die(mysql_error());
									
									$tot_inorder = $tot_inorder + $cancelled_qtyArr[$i];
									if($sales_typeArr[$i]==0){$tot_free_allocation = $tot_free_allocation + $cancelled_qtyArr[$i];}
									
									$quantity = $quantity - $cancelled_qtyArr[$i];
									mysql_query("Update cancelled_po set reactiveated='1' where serialnumber='$cancellation_number[$i]'") or die(mysql_error());
								}
							}
						
							if($quantity>0)
							{
								$query3 = "Insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
								cre_datetime,activestatus) values('$po_det_last_id','$sc_detserialnumber_fk','$quantity',0,1,'$VObject->cre_userid_fk',
								'$VObject->cre_datetime',1)";
								mysql_query($query3) or die(mysql_error());
								
								$tot_inorder = $tot_inorder + $quantity;
								$tot_free_allocation = $tot_free_allocation + $quantity;
							}		
							
							//Update Inorder Details For Self
							$query4 = "select in_order,free_for_allocation from stock_header where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
							$result4 = mysql_query($query4) or die(mysql_error());
							while ($row4 = mysql_fetch_object($result4)) 
							{
									$in_order = (int) $row4->in_order;
									$free_for_allocation = (int) $row4->free_for_allocation;
									
									$pre_inorder_qty = $in_order;
									$pre_free_allocation_qty = $free_for_allocation;
									
									$in_order = $in_order + $tot_inorder;
									$free_for_allocation = $free_for_allocation + $tot_free_allocation;
									
									$post_inorder_qty = $in_order;
									$post_free_allocation_qty = $free_for_allocation;
									$inorder_det_qty = $post_inorder_qty - $pre_inorder_qty;
									$free_allocation_det_qty = $post_free_allocation_qty - $pre_free_allocation_qty;
						
								$query5 = "update stock_header set in_order='$in_order',free_for_allocation='$free_for_allocation' where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
								$s5 = mysql_query($query5) or die(mysql_error());
								
								$query6 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$po_det_last_id',1,'$value',0,0,'$inorder_det_qty',0,'$free_allocation_det_qty',1)";
								$s6 = mysql_query($query6) or die(mysql_error());
							}
						}							
				}
			}
		}
		return $po_id;
	}
	
    public function editPO($VObject) 
	{
        $po_id = "";
        if ($VObject->po_status != "DRAFT") {
            $po_id = $this->generatePoCode();
            mysql_query("insert into po_code_table(po_code) values ('$po_id')") or die(mysql_error());
        } else {
            $po_id = "Draft";
        }
        $query = "update po_header set po_id='$po_id',supplier_serialnumber_fk='$VObject->supplier_serialnumber_fk',total_amount='$VObject->total_amount',total_qty='$VObject->total_qty',po_term='$VObject->po_term',po_gst='$VObject->po_gst',currency_serialnumber_fk='$VObject->currency_serialnumber_fk',current_rate='$VObject->current_rate',po_issue_date='$VObject->po_issue_date',po_confirm_date='$VObject->po_confirm_date',no_of_days='$VObject->no_of_days',expected_datetime='$VObject->expected_datetime',po_status='$VObject->po_status',po_remarks='$VObject->po_remarks' where po_serialnumber='$VObject->po_serialnumber' and activestatus=1";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) 
		{
            mysql_query("update enq_det_po set activestatus=0 where po_serialnumber_fk='$VObject->po_serialnumber'") or die(mysql_error());
            mysql_query("update po_details set activestatus=0 where po_serialnumber_fk='$VObject->po_serialnumber'") or die(mysql_error());
            foreach ($VObject->enq_detserialnumber_fk as $r => $value) {
                $query1 = "insert into enq_det_po(enq_detserialnumber_fk,po_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$value','$VObject->po_serialnumber','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query1) or die(mysql_error());
            }
           foreach ($VObject->pro_serialnumber_fk as $r => $value) 
		    {
				$sc_detserialnumber_fk = mysql_real_escape_string($VObject->sc_detserialnumber_fk[$r]);
				$self_sc = mysql_real_escape_string($VObject->self_sc[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $unit_price = mysql_real_escape_string($VObject->unit_price[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
                $warehouse_serialnumber_fk = mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into po_details(po_serialnumber_fk,sc_detserialnumber_fk,pro_serialnumber_fk,pro_qty,unit_price,pro_total_price,pro_status,warehouse_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$VObject->po_serialnumber','$sc_detserialnumber_fk','$value','$pro_qty','$unit_price','$pro_total_price','$pro_status','$warehouse_serialnumber_fk','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$po_det_last_id = mysql_insert_id();
				
                if ($VObject->po_status != "DRAFT") 
				{
					if($self_sc==0)
					{
						$sc_type_result = mysql_query("Select if(sales_type='Ex-Stock Sales',1,if(sales_type='Advanced Sales',2,3)) as sc_type from sales_conformation_details where detserialnumber='$sc_detserialnumber_fk' and activestatus=1") or die(mysql_error());
						$row = mysql_fetch_object($sc_type_result);
						$sc_type = (int)$row->sc_type;
						
						$query3 = "insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
						cre_datetime,activestatus) values('$po_det_last_id','$sc_detserialnumber_fk','$pro_qty','$sc_type',0,'$VObject->cre_userid_fk',
						'$VObject->cre_datetime',1)";
						mysql_query($query3) or die(mysql_error());
						
						//Update Inorder Details For Customer
							$query4 = "select in_order from stock_header where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
							$result4 = mysql_query($query4) or die(mysql_error());
							while ($row4 = mysql_fetch_object($result4)) 
							{							
								$in_order = (int) $row4->in_order;
									
								$pre_inorder_qty = $in_order;
									
								$in_order = $in_order + $pro_qty;
									
								$post_inorder_qty = $in_order;
									
								$inorder_det_qty = $post_inorder_qty - $pre_inorder_qty;
								
								$query5 = "update stock_header set in_order='$in_order' where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
								$s1 = mysql_query($query5) or die(mysql_error());
								
								$query6 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$po_det_last_id',1,'$value',0,0,'$inorder_det_qty',0,0,1)";
								$s6 = mysql_query($query6) or die(mysql_error());
							}
					}else
						{
							
							$query3 = "insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
							cre_datetime,activestatus) values('$po_det_last_id','$sc_detserialnumber_fk','$pro_qty',0,1,'$VObject->cre_userid_fk',
							'$VObject->cre_datetime',1)";
							mysql_query($query3) or die(mysql_error());
					
							//Update Inorder Details For Self
							$query4 = "select in_order,free_for_allocation from stock_header where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
							$result4 = mysql_query($query4) or die(mysql_error());
							while ($row4 = mysql_fetch_object($result4)) 
							{
									$in_order = (int) $row4->in_order;
									$free_for_allocation = (int) $row4->free_for_allocation;
									
									$pre_inorder_qty = $in_order;
									$pre_free_allocation_qty = $free_for_allocation;
									
									$in_order = $in_order + $pro_qty;
									$free_for_allocation = $free_for_allocation + $pro_qty;
									
									$post_inorder_qty = $in_order;
									$post_free_allocation_qty = $free_for_allocation;
									$inorder_det_qty = $post_inorder_qty - $pre_inorder_qty;
									$free_allocation_det_qty = $post_free_allocation_qty - $pre_free_allocation_qty;
						
								$query5 = "update stock_header set in_order='$in_order',free_for_allocation='$free_for_allocation' where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
								$s1 = mysql_query($query5) or die(mysql_error());
								
								$query6 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$po_det_last_id',1,'$value',0,0,'$inorder_det_qty',0,'$free_allocation_det_qty',1)";
								$s6 = mysql_query($query6) or die(mysql_error());
							}
						}							
				}
			}
		}
		return $po_id;
	}

    public function deletePO($po_serialnumber) 
	{
        $query = "update po_header set activestatus=0 where po_serialnumber='$po_serialnumber'";
        mysql_query($query) or die(mysql_error());
        $query1 = "update enq_det_po set activestatus=0 where po_serialnumber_fk='$po_serialnumber'";
        mysql_query($query1) or die(mysql_error());
        $query2 = "update  po_details set activestatus=0 where po_serialnumber_fk='$po_serialnumber'";
        mysql_query($query2) or die(mysql_error());
        return $po_serialnumber;
    }
	

    public function getPoByCode($po_serialnumber) {
        $ret = array();
        $query = "select ph.po_serialnumber,ph.po_id,ph.supplier_serialnumber_fk,ph.total_amount,ph.total_qty,ph.po_term,ph.po_gst,ph.currency_serialnumber_fk,ph.current_rate,ph.po_issue_date,
		ph.po_confirm_date,ph.no_of_days,ph.expected_datetime,ph.po_status,ph.po_remarks,ph.cre_userid_fk,ph.cre_datetime,ph.activestatus,ch.client_name as supplier from po_header as ph LEFT JOIN client_header as ch ON 
		ch.activestatus=1 and ch.serialnumber=ph.supplier_serialnumber_fk where ph.activestatus=1 and ph.po_serialnumber='$po_serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPurchaseOrder();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->supplier_serialnumber_fk = (int) $row->supplier_serialnumber_fk;
            $temp->total_amount = (double) $row->total_amount;
            $temp->total_qty = (int) $row->total_qty;
			$temp->po_term = (int) $row->po_term;
			$temp->po_gst = (double) $row->po_gst;
			$temp->currency_serialnumber_fk = (int) $row->currency_serialnumber_fk;
			$temp->current_rate = (double) $row->current_rate;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->po_confirm_date = date("d-m-Y", strtotime($row->po_confirm_date));
            $temp->no_of_days = (int) $row->no_of_days;
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->po_status = $row->po_status;
            $temp->po_remarks = $row->po_remarks;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			$temp->client_name = $row->supplier;

            $query1 = "select emp.enq_det_po_serialnumber,emp.enq_detserialnumber_fk,ed.enq_serialnumber_fk, 
			ed.pro_qty,ed.pro_serialnumber_fk,eh.enq_code from enq_det_po as emp LEFT JOIN enquiry_detail as ed ON 
			emp.enq_detserialnumber_fk=ed.enq_detserialnumber and ed.activestatus=1 LEFT JOIN enquiry_header as eh ON
			eh.enq_serialnumber=ed.enq_serialnumber_fk and eh.activestatus=1 where emp.po_serialnumber_fk='$row->po_serialnumber' 
			and emp.activestatus=1";
            $s1 = mysql_query($query1) or die(mysql_error());
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->enq_det_po_serialnumber[] = (int) $row1->enq_det_po_serialnumber;
				$temp->enq_serialnumber_fk[] = (int) $row1->enq_serialnumber_fk;
				$temp->enq_code[] = $row1->enq_code;
                $temp->enq_detserialnumber_fk[] = (int) $row1->enq_detserialnumber_fk;
				$temp->enq_pro_serialnumber_fk[] = (int) $row1->pro_serialnumber_fk;
				$temp->enq_pro_qty[] = (int)$row1->pro_qty;
            }
			
            $query2 = "Select pd.po_serialnumber_fk,pd.po_detserialnumber,pd.sc_detserialnumber_fk,IFNULL(sh.sales_con_code,'AESC') as sales_con_code,IFNULL(IF(sh.customer_serialnumber_fk=0,1,0),1) as self_sc,pd.pro_serialnumber_fk,pd.pro_qty,
			pd.unit_price,pd.pro_total_price,pd.pro_status,pd.warehouse_serialnumber_fk,wh.warehouse_name,ph.pro_name,ph.part_number,ph.pro_description,ph.model_no from po_details as pd LEFT JOIN product_header as ph ON 
			ph.serialnumber=pd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON 
			sh.activestatus=1 and sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk and wh.activestatus=1 where pd.po_serialnumber_fk='$row->po_serialnumber' 
			and pd.activestatus=1";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) {
                $temp->po_serialnumber_fk[] = (int) $row2->po_serialnumber_fk;
                $temp->po_detserialnumber[] = (int) $row2->po_detserialnumber;
				$temp->sc_detserialnumber_fk[] = (int) $row2->sc_detserialnumber_fk;
				$temp->self_sc[] = (int) $row2->self_sc;
				$temp->sales_con_code[] = $row2->sales_con_code;
                $temp->pro_serialnumber_fk[] = (int) $row2->pro_serialnumber_fk;
                $temp->pro_qty[] = (int) $row2->pro_qty;
                $temp->unit_price[] = (double) $row2->unit_price;
                $temp->pro_total_price[] = (double) $row2->pro_total_price;
                $temp->pro_status[] = $row2->pro_status;
                $temp->warehouse_serialnumber_fk[] = (int) $row2->warehouse_serialnumber_fk;
				$temp->warehouse_name[] = $row2->warehouse_name;
                $temp->pro_name[] = $row2->pro_name;
				$temp->part_number[] = $row2->part_number;
				$temp->pro_description[] = $row2->pro_description;
            }
            $ret[] = $temp;
            mysql_free_result($s1);
            mysql_free_result($s2);
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getAllPO() {
        $ret = array();
        $query = "select ph.po_serialnumber,ph.po_id,ph.supplier_serialnumber_fk,ph.total_amount,ph.total_qty,ph.po_term,ph.po_gst,ph.currency_serialnumber_fk,ph.current_rate,ph.po_issue_date,
		ph.po_confirm_date,ph.no_of_days,ph.expected_datetime,ph.po_status,ph.po_remarks,ph.cre_userid_fk,ph.cre_datetime,ph.activestatus,ch.client_name as supplier from po_header as ph LEFT JOIN client_header as ch ON 
		ch.activestatus=1 and ch.serialnumber=ph.supplier_serialnumber_fk where ph.activestatus=1 and ph.po_status!='VOID'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPurchaseOrder();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->supplier_serialnumber_fk = (int) $row->supplier_serialnumber_fk;
            $temp->total_amount = (double) $row->total_amount;
            $temp->total_qty = (int) $row->total_qty;
			$temp->po_term = (int) $row->po_term;
			$temp->po_gst = (double) $row->po_gst;
			$temp->currency_serialnumber_fk = (int) $row->currency_serialnumber_fk;
			$temp->current_rate = (double) $row->current_rate;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->po_confirm_date = date("d-m-Y", strtotime($row->po_confirm_date));
            $temp->no_of_days = (int) $row->no_of_days;
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->po_status = $row->po_status;
            $temp->po_remarks = $row->po_remarks;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			$temp->client_name = $row->supplier;

            $query1 = "select emp.enq_det_po_serialnumber,emp.enq_detserialnumber_fk,ed.enq_serialnumber_fk, 
			ed.pro_qty,ed.pro_serialnumber_fk,eh.enq_code from enq_det_po as emp LEFT JOIN enquiry_detail as ed ON 
			emp.enq_detserialnumber_fk=ed.enq_detserialnumber and ed.activestatus=1 LEFT JOIN enquiry_header as eh ON
			eh.enq_serialnumber=ed.enq_serialnumber_fk and eh.activestatus=1 where emp.po_serialnumber_fk='$row->po_serialnumber' 
			and emp.activestatus=1";
            $s1 = mysql_query($query1) or die(mysql_error());
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->enq_det_po_serialnumber[] = (int) $row1->enq_det_po_serialnumber;
				$temp->enq_serialnumber_fk[] = (int) $row1->enq_serialnumber_fk;
				$temp->enq_code[] = $row1->enq_code;
                $temp->enq_detserialnumber_fk[] = (int) $row1->enq_detserialnumber_fk;
				$temp->enq_pro_serialnumber_fk[] = (int) $row1->pro_serialnumber_fk;
				$temp->enq_pro_qty[] = (int)$row1->pro_qty;
            }
			
            $query2 = "Select pd.po_serialnumber_fk,pd.po_detserialnumber,pd.sc_detserialnumber_fk,IFNULL(sh.sales_con_code,'AESC') as sales_con_code,IFNULL(IF(IFNULL(sh.customer_serialnumber_fk,0)=0 or pd.sc_detserialnumber_fk=0,1,0),1) as self_sc,
			pd.pro_serialnumber_fk,pd.pro_qty,pd.unit_price,pd.pro_total_price,pd.pro_status,pd.warehouse_serialnumber_fk,wh.warehouse_name,ph.pro_name,ph.part_number,ph.stamp_number,ph.pro_description,ph.model_no from po_details as pd LEFT JOIN product_header as ph ON 
			ph.serialnumber=pd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.activestatus=1 and 
			sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk and wh.activestatus=1 where pd.po_serialnumber_fk='$row->po_serialnumber' and pd.activestatus=1";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) {
                $temp->po_serialnumber_fk[] = (int) $row2->po_serialnumber_fk;
                $temp->po_detserialnumber[] = (int) $row2->po_detserialnumber;
				$temp->sc_detserialnumber_fk[] = (int) $row2->sc_detserialnumber_fk;
				$temp->self_sc[] = (int) $row2->self_sc;
				$temp->sales_con_code[] = $row2->sales_con_code;
                $temp->pro_serialnumber_fk[] = (int) $row2->pro_serialnumber_fk;
                $temp->pro_qty[] = (int) $row2->pro_qty;
                $temp->unit_price[] = (double) $row2->unit_price;
                $temp->pro_total_price[] = (double) $row2->pro_total_price;
                $temp->pro_status[] = $row2->pro_status;
                $temp->warehouse_serialnumber_fk[] = (int) $row2->warehouse_serialnumber_fk;
				$temp->warehouse_name[] = $row2->warehouse_name;
                $temp->pro_name[] = $row2->pro_name;
				$temp->part_number[] = $row2->part_number;
				$temp->stamp_number[] = $row2->stamp_number;
				$temp->pro_description[] = $row2->pro_description;
            }
            $ret[] = $temp;
            mysql_free_result($s1);
            mysql_free_result($s2);
        }
        mysql_free_result($s);
        return $ret;
    }

    public function generatePoCode() {
        $res = "";
        $query = "select po_code from po_code_table order by po_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("PO", $row[0]);
            $num = intval($str[1]);
            $res = "PO" . ++$num;
        } else {
            $res = 'PO1001';
        }
        return $res;
    }

}

class VoStatePurchaseOrder {
	var $po_serialnumber;
    var $po_id;
	var $po_detserialnumber;
	var $sc_detserialnumber_fk;
	var $sales_con_code;
	var $pro_serialnumber_fk;
	var $pro_name;
	var $part_number;
	var $pro_description;	
	var $po_qty;
	var $balance_qty;
	var $unit_price;
	var $warehouse_serialnumber_fk;
	var $sampe_po;
}

class VoPurchaseOrder {
	var $po_serialnumber;
	var $po_id;
    var $supplier_serialnumber_fk; 
	var $total_amount;
	var $total_qty;
	var $po_term;
    var $po_gst;
    var $currency_serialnumber_fk;
    var $current_rate;
    var $po_issue_date;
    var $po_confirm_date;
	var $no_of_days;
    var $expected_datetime;
	var $po_status;
    var $po_remarks;
    var $enq_det_po_serialnumber;
	var $enq_code;
	var $enq_serialnumber_fk;
	var $enq_pro_serialnumber_fk;
	var	$enq_detserialnumber_fk;
	var $enq_pro_qty;
    var $po_serialnumber_fk;
    var $po_detserialnumber;
	var $sales_con_code;
	var $self_sc;
	var $sc_detserialnumber_fk;
    var $pro_serialnumber_fk;
	var $pro_name;
	var $stamp_number;
	var $part_number;
	var $pro_description;
    var $pro_qty;
    var $unit_price;
    var $pro_total_price;
    var $pro_status;
    var $warehouse_serialnumber_fk;
	var $warehouse_name;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;
}

class VoSalesConformation {
	var $serialnumber;	  
	var $quot_det_sc_serialnumber;
    var $quot_detserialnumber_fk;
	var $quotation_serialnumber_fk;
	var $quotation_code;
	var $quot_sc_serialnumber_fk;
	var $quot_pro_serialnumber_fk;
	var $quot_pro_qty;
    var $customer_serialnumber_fk;
    var $client_name;
    var $total_amount;
    var $total_qty;
	var $sc_term;
	var $sc_gst;
	var $currency_serialnumber_fk;
	var $current_rate;
    var $sales_con_status;
    var $sales_con_date;
	var $no_of_days;
	var $expected_datetime;
    var $sc_remarks;
    var $detserialnumber;
    var $sc_serialnumber_fk;
    var $pro_serialnumber_fk;
	var $pro_detserialnumber;
	var $base_currency_detserialnumber_fk;
	var $pro_name;
	var $pro_description;
    var $part_number;
    var $model_no;
	var $default_price;
	var $sgd_listprice;
	var $price_list;
    var $pro_qty;
    var $price_per_unit;
    var $pro_total_price;
    var $pro_status;
    var $delivery_loc;
    var $warehouse_serialnumber_fk;
    var $sales_type;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;
}

class VoUnMappedIndentSc {
	var $sc_serialnumber;
	var $self_sc;
	var $sales_con_code;
	var $sc_detserialnumber;
	var $pro_qty;
	var $pro_serialnumber_fk;
	var $pro_name;
	var $part_number;
	var $stamp_number;
	var $pro_description;	 
	var $sgd_listprice;
	var $isSelected;
}

class VoInsertedSc
{
var $sales_con_code;
var $pro_serialnumber_fk;
var $sc_detserialnumber_fk;
}

?>